﻿using AchieveCommon;
using AchieveDALFactory;
using AchieveEntity;
using AchieveInterfaceDAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Timers;

namespace AchieveBLL
{
    public class BomBLL
    {
        /// <summary>
        /// 根据物料id查找bomid
        /// </summary>
        /// <param name="fitemid"></param>
        /// <returns></returns>
        public static int getBomid(int fitemid)
        {
            string sql1 = "select finterid from icbom where fitemid=" + fitemid;
            object o = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql1);
            if (o is DBNull)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(o);
            }
        }

     
        /// <summary>
        /// 根据物料编码查询Bom子物料清单；
        /// </summary>
        /// <param name="FNumber"></param>
        /// <returns></returns>
        public static DataTable getChildItem(string FNumber) {
            string sql = @"select d.FItemID,d.FNumber,d.FModel,d.FName from t_ICITEMCORE a right join icbom b on a.FItemID=b.FItemID 
right join icbomchild c on c.FInterID=b.FInterID left join t_ICITEMCORE d on c.FItemID=d.FItemID  where a.FNumber='{0}'";
            sql = string.Format(sql, FNumber);
            return SqlHelper.GetDataTableK3(sql);
        }

        /// <summary>
        /// 根据物料id获取子物料（含后代）bom树列表；
        /// </summary>
        /// <param name="BomID"></param>
        /// <returns></returns>
        private static List<bomTree> getChildByBomID(int BomID)
        {

            string sql = @"select a.FinterID,a.FItemID,b.FModel,a.FQty,b.FName,b.FNumber,b.FOrderPrice,isnull(c.FinterID,0) as cFinterID,                   
                        a.FENTRYSELFZ0143 as itemPrice,a.FENTRYSELFZ0144 as worktime,d.fnetweight,d.FGrossWeight 
                        from icbomchild a left Join t_ICITEMCORE b on a.FItemID=b.FItemID 
                        left join icbom c on c.fitemid=a.fitemid
                        left join t_icitemdesign d on  a.FItemID=d.FItemID
                        where a.FinterID=" + BomID; 

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStrK3, sql);
            List<bomTree> btlist = new List<bomTree>();
            if (dt.Rows.Count > 0)//存在子物料
            {
                //添加子物料信息
                foreach (DataRow item in dt.Rows)
                {
                    bomTree bt = new bomTree();
                    bt.FinterID = (Int32)item["cFinterID"];
                    bt.FItemID = (Int32)item["FItemID"];
                    bt.FName = item["FName"].ToString();
                    bt.FModel = item["FModel"].ToString();
                    bt.FNumber = item["FNumber"].ToString();

                    bt.FOrderPrice = item["FOrderPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FOrderPrice"]);
                    bt.fnetweight = item["fnetweight"] == DBNull.Value ? 0 : Convert.ToDecimal(item["fnetweight"]);
                    bt.itemPrice = item["itemPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(item["itemPrice"]);
                    bt.worktime = item["worktime"] == DBNull.Value ? 0 : Convert.ToDecimal(item["worktime"]);
                    bt.FGrossWeight = item["FGrossWeight"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FGrossWeight"]);
                    bt.FQty = item["FQty"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FQty"]); 
     
                    if (bt.FinterID > 0)//存在物料编码
                    {
                        //递归查找
                        bt.children = getChildByBomID(bt.FinterID);
                    }
                    btlist.Add(bt);

                }
            }
            else
            {
                return btlist;

            }
            return btlist;

        }

        /// <summary>
        /// 根据物料id获取完整的bom树形结构，具有唯一根节点；
        /// 递归编程，不建议使用！！
        /// </summary>
        /// <param name="itemID"></param>
        /// <returns></returns>
        public static bomTree getBomTreeByItemID_old(int itemID)
        {
            bomTree bt = new bomTree();
            //获取根物料的信息
            string sql = @"select a.FItemID,FName,a.FNumber, a.FModel,FOrderPrice,b.FinterID,b.FHEADSELFZ0132 as itemPrice,b.FHEADSELFZ0133 as worktime,c.fnetweight,c.FGrossWeight  
                        from t_ICITEMCORE a left join icbom b on a.FItemID=b.FItemID 
                         left join t_icitemdesign c on  a.FItemID=c.FItemID
                        where b.FinterID is not null and a.FItemID=" + itemID;

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStrK3, sql);
            int c = dt.Rows.Count;
            if (c > 0)
            {
                bt.FinterID = (int)dt.Rows[0]["FinterID"];//bomid
                bt.FItemID = itemID;
                bt.FName = dt.Rows[0]["FName"].ToString();
                bt.FNumber = dt.Rows[0]["FNumber"].ToString();//物料编码，界面业务用
                bt.FModel = dt.Rows[0]["FModel"].ToString();

                bt.FOrderPrice = dt.Rows[0]["FOrderPrice"]==DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[0]["FOrderPrice"]);
                bt.fnetweight = dt.Rows[0]["fnetweight"] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[0]["fnetweight"]);
                bt.itemPrice = dt.Rows[0]["itemPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[0]["itemPrice"]);
                bt.worktime = dt.Rows[0]["worktime"] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[0]["worktime"]);
                bt.FGrossWeight = dt.Rows[0]["FGrossWeight"] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[0]["FGrossWeight"]); 
 
                
                bt.children = getChildByBomID(bt.FinterID);// bt.children.Add();
            }
            else
            {
                bt.FName = "不存在该物料Bom信息！";
                return bt;
            }

            return bt;
        }

        /// <summary>
        /// 数据行转bom树的基本函数，重要
        /// </summary>
        /// <param name="r"></param>
        /// <returns></returns>
        private static bomTree getBomTreeByDataRow(DataRow r)
        {
            bomTree bt = new bomTree();
           //输如表结构：m.BomLevel,FFItemID（父物料ID）FItemID（当前物料ID）,FInterID（父BomID）,FQty,FAuxQty,FName,FNumber,
        ///FOrderPrice,a.FModel,InterID（当前BomID）,FHEADSELFZ0132,FHEADSELFZ0133,（ICBOM表中的价格、工时itemPrice，worktime）、
        ///FENTRYSELFZ0143,FENTRYSELFZ0144,（icbomchild 表中的价格、工时itemPrice，worktime）d.FNetWeight,d.FGrossWeight
            bt.parentId = (int)r["FFInterID"];
            bt.FinterID = r["FInterID"] == DBNull.Value ? 0 : Convert.ToInt32(r["FInterID"]);
            bt.level = (int)r["BomLevel"];
            bt.FItemID = (int)r["FItemID"];
            bt.FName = r["FName"].ToString();
            bt.FNumber = r["FNumber"].ToString();//物料编码，界面业务用
            bt.FModel = r["FModel"].ToString();
            bt.FOrderPrice = r["FOrderPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(r["FOrderPrice"]);
            bt.fnetweight = r["FNetWeight"] == DBNull.Value ? 0 : Convert.ToDecimal(r["FNetWeight"]);
            bt.itemPrice = r["FENTRYSELFZ0143"] == DBNull.Value ? 0 : Convert.ToDecimal(r["FENTRYSELFZ0143"]);//价格、工时等从icbomchild表中取值；
            bt.worktime = r["FENTRYSELFZ0144"] == DBNull.Value ? 0 : Convert.ToDecimal(r["FENTRYSELFZ0144"]);
            bt.FGrossWeight = r["FGrossWeight"] == DBNull.Value ? 0 : Convert.ToDecimal(r["FGrossWeight"]);
            return bt;
        }


        public static bomTree searchBomTreeByID(bomTree bt, int bomid){
            if (bt.FinterID==bomid)
            {
                return bt;
            }
            else
            {
                foreach (bomTree item in bt.children)
                {
                 return   searchBomTreeByID(item, bomid);
                }
            }
            return null;        
        }

        /// <summary>
        /// 数据表转bom树的递归函数
        /// </summary>
        /// <param name="level"></param>
        /// <param name="dt"></param>
        /// <param name="bt"></param>
        private static void bomTreeAddChild(int level, DataTable dt, ref bomTree bt) {

            DataRow[] rs = dt.Select("BomLevel=" + level);
            if (rs.Length == 0)
            {
                return;
            }
            else
            { //添加子bom树；
                foreach (DataRow item in rs)
                {
                    if (bt.FItemID==Convert.ToInt32(item["FFItemID"]))
                    {
                        bomTree bo = getBomTreeByDataRow(item);
                        bomTreeAddChild(level + 1, dt, ref bo);
                        bt.children.Add(bo);
                      
                     } 
                } 
            } 
            
        }



        //从bom表中递归生成树结构，重要！
        public static bomTree getBomTreeByItemID(int itemID)
        {
            bomTree bt = new bomTree();            
            DataTable dt = getBomTableByItemID(itemID);
            if (dt.Rows.Count > 0)
            {
                DataRow r = dt.Select("BomLevel=0")[0];
                bt = getBomTreeByDataRow(r);
                bomTreeAddChild(1, dt, ref bt);
            }
            else {
                bt.FName = "无此物料信息";
            }           
            return bt;
        }



        private static List<bomTree> getChildByBomID(int BomID,DataTable dtBom)
        {

            string sql = @"select a.FinterID,a.FItemID,b.FModel,a.FQty,b.FName,b.FNumber,b.FOrderPrice,isnull(c.FinterID,0) as cFinterID,                   
                        a.FENTRYSELFZ0143 as itemPrice,a.FENTRYSELFZ0144 as worktime,d.fnetweight,d.FGrossWeight 
                        from icbomchild a left Join t_ICITEMCORE b on a.FItemID=b.FItemID 
                        left join icbom c on c.fitemid=a.fitemid
                        left join t_icitemdesign d on  a.FItemID=d.FItemID
                        where a.FinterID=" + BomID;

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStrK3, sql);
            List<bomTree> btlist = new List<bomTree>();
            if (dt.Rows.Count > 0)//存在子物料
            {
                //添加子物料信息
                foreach (DataRow item in dt.Rows)
                {
                    bomTree bt = new bomTree();
                    bt.FinterID = (Int32)item["cFinterID"];
                    bt.FItemID = (Int32)item["FItemID"];
                    bt.FName = item["FName"].ToString();
                    bt.FModel = item["FModel"].ToString();
                    bt.FNumber = item["FNumber"].ToString();

                    bt.FOrderPrice = item["FOrderPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FOrderPrice"]);
                    bt.fnetweight = item["fnetweight"] == DBNull.Value ? 0 : Convert.ToDecimal(item["fnetweight"]);
                    bt.itemPrice = item["itemPrice"] == DBNull.Value ? 0 : Convert.ToDecimal(item["itemPrice"]);
                    bt.worktime = item["worktime"] == DBNull.Value ? 0 : Convert.ToDecimal(item["worktime"]);
                    bt.FGrossWeight = item["FGrossWeight"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FGrossWeight"]);
                    bt.FQty = item["FQty"] == DBNull.Value ? 0 : Convert.ToDecimal(item["FQty"]);

                    if (bt.FinterID > 0)//存在物料编码
                    {
                        //递归查找
                        bt.children = getChildByBomID(bt.FinterID);
                    }
                    btlist.Add(bt);

                }
            }
            else
            {
                return btlist;

            }
            return btlist;

        }

        /// <summary>
        /// 根据物料id查询bom清单，BomLevel=1，为子物料，0为根物料
        /// 输出表结构：m.BomLevel,FFItemID（父物料ID）FItemID（当前物料ID）,FFInterID（父BomID）,FQty,FAuxQty,FName,FNumber,
        ///FOrderPrice,a.FModel,FInterID（当前BomID）,FHEADSELFZ0132,FHEADSELFZ0133,（ICBOM表中的价格、工时itemPrice，worktime）、
        ///FENTRYSELFZ0143,FENTRYSELFZ0144,（icbomchild 表中的价格、工时itemPrice，worktime）d.FNetWeight,d.FGrossWeight
        /// </summary>
        /// <param name="itemID"></param>
        /// <returns></returns>
        public static DataTable getBomTableByItemID(int itemID) {
            string sql = @"
;with t(FFItemID,FItemID,FInterID,BomLevel ) as ( 
select  a.FItemID FFItemID,b.FItemID,b.FInterID,1 BomLevel 
from icbom a 
join icbomchild b on a.FInterID=b.FInterID 
where a.FItemID=@FItemID
union all
select  a.FItemID FFItemID,b.FItemID,b.FInterID,1+BomLevel BomLevel
from icbom a 
join icbomchild b on a.FInterID=b.FInterID 
join t on a.FItemID= t.FItemID )
select m.BomLevel,m.FFItemID,m.FItemID,m.FInterID FFInterID,b.FQty,b.FAuxQty,a.FName,a.FNumber,
a.FOrderPrice,a.FModel,c.FInterID FInterID,FHEADSELFZ0132,FHEADSELFZ0133,FENTRYSELFZ0143,FENTRYSELFZ0144,d.FNetWeight,d.FGrossWeight
from 
(select 0 FFItemID,@FItemID FItemID,0 FInterID,0 BomLevel
union all
select FFItemID,FItemID,FInterID,BomLevel from t) m
left join  t_ICITEMCORE a on a.FItemID=m.FItemID
left join  icbomchild b on b.FItemID=m.FItemID and b.FInterID=m.FInterID
left join icbom c on c.FItemID=m.FItemID 
left join t_icitemdesign d on  d.FItemID=m.FItemID
order by BomLevel asc";
            SqlParameter sp = new SqlParameter("@FItemID", itemID);
            try
            {
                return SqlHelper.GetDataTableK3(sql, sp);
            }
            catch (Exception)
            {
                
                throw;
            }

        }


        /// <summary>
        /// 将一个Bom树递归显示成表格；
        /// </summary>
        /// <param name="bt"></param>
        /// <returns></returns>
        public static DataTable bomTree2Table(bomTree bt) {
            DataTable dt = new DataTable();
          //  dt.Columns.Add("序号",typeof(int));
            dt.Columns.Add("物料ID", typeof(string));
            dt.Columns.Add("物料代码", typeof(string));
            dt.Columns.Add("名称", typeof(string));
            dt.Columns.Add("规格型号", typeof(string));
            dt.Columns.Add("数量", typeof(decimal));
            dt.Columns.Add("材料价格", typeof(decimal));
            dt.Columns.Add("工时", typeof(decimal));
            dt.Columns.Add("重量", typeof(decimal));
            dt.Rows.Add(bt.FNumber, bt.FName, bt.FModel, bt.FQty, bt.itemPrice, bt.worktime, bt.FGrossWeight);
            if (bt.children != null)
            {
                getnoderows(bt.children, ref dt); 
            } 
            return dt;
        }

        public static void getnoderows(List<bomTree> list, ref DataTable dt)
        { 
            foreach (bomTree bt in list)
            {
                dt.Rows.Add(bt.FItemID, bt.FNumber, bt.FName, bt.FModel, bt.FQty, bt.itemPrice, bt.worktime, bt.FGrossWeight,99); 

                if (bt.children != null)
                {
                    getnoderows(bt.children, ref dt);
                } 
            }
        }
        /// <summary>
        /// bom树形结构数据转换为json
        /// </summary>
        /// <param name="bt"></param>
        /// <returns></returns>
        public static string bomTree2Json(bomTree bt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            sb.Append(string.Format("\"name\":\"{0}\",", bt.name));
            sb.Append(string.Format("\"FinterID\":\"{0}\",", bt.FinterID));
            sb.Append(string.Format("\"FItemID\":\"{0}\",", bt.FItemID));
            sb.Append(string.Format("\"FName\":\"{0}\",", bt.FName));
            sb.Append(string.Format("\"FNumber\":\"{0}\",", bt.FNumber));

            sb.Append(string.Format("\"FModel\":\"{0}\",", bt.FModel));

            sb.Append(string.Format("\"FQty\":{0},", bt.FQty));
            sb.Append(string.Format("\"FOrderPrice\":{0},", bt.FOrderPrice));
            sb.Append(string.Format("\"itemPrice\":{0},", bt.itemPrice));//材料价格 
            sb.Append(string.Format("\"worktime\":{0},", bt.worktime));//工时  
            sb.Append(string.Format("\"fnetweight\":{0},", bt.fnetweight));//净重  
            sb.Append(string.Format("\"FGrossWeight\":{0},", bt.FGrossWeight));//净重 _最新 
            

            sb.Append(string.Format("\"value\":{0}", bt.value));//根据需要取值，目前不用
            if (bt.children.Count>0)
            {
                sb.Append(string.Format(",\"children\":{0}", getnodejson(bt.children)));
            }
            sb.Append("}");

            return sb.ToString();
            //sb.Append(string.Format("children"))       
        }

        //bom转为json的递归函数
        private static string getnodejson(List<bomTree> list)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("[");
            foreach (bomTree item in list)
            {
                StringBuilder sb1 = new StringBuilder();
                sb1.Append("{");
                sb1.Append(string.Format("\"name\":\"{0}\",", item.name));
                sb1.Append(string.Format("\"FinterID\":\"{0}\",", item.FinterID));
                sb1.Append(string.Format("\"FItemID\":\"{0}\",", item.FItemID));
                sb1.Append(string.Format("\"FName\":\"{0}\",", item.FName));
                sb1.Append(string.Format("\"FModel\":\"{0}\",", item.FModel));
                sb1.Append(string.Format("\"FNumber\":\"{0}\",", item.FNumber));



                sb1.Append(string.Format("\"FQty\":{0},", item.FQty));
                sb1.Append(string.Format("\"FOrderPrice\":{0},", item.FOrderPrice));
                sb1.Append(string.Format("\"itemPrice\":{0},", item.itemPrice));//材料价格 
                sb1.Append(string.Format("\"worktime\":{0},", item.worktime));//工时  
                sb1.Append(string.Format("\"fnetweight\":{0},", item.fnetweight));//净重  
                sb1.Append(string.Format("\"FGrossWeight\":{0},", item.FGrossWeight));//净重                  
                sb1.Append(string.Format("\"value\":{0}", item.value));

                if (item.children.Count>0)
                {
                    sb1.Append(string.Format(",\"children\":{0}", getnodejson(item.children)));
                }

                sb1.Append("},");
                sb.Append(sb1.ToString());

            }
            sb.Replace(',', ']', sb.Length - 1, 1);
            return sb.ToString();
        }




    }


    /// <summary>
    /// bomTree 类
    /// </summary>
    public class bomTree
    {
        public bomTree()
        {

        }
        public bomTree(int finterid, string fname)
        {
            this.FinterID = finterid;
            this.FName = fname;
        }
        public string name
        {
            get { return this.FName; }

        }

        public int value
        {
            get { return this.FinterID; }

        }
        public int parentId;
        public int level;//0为根；
        public int FinterID;//Bomid
        public int FItemID;//物料编码
        public string FName;//物料名称
        public string FModel;//规格型号
        public string FNumber;//物料代码，前端业务用 
        public decimal FQty = new decimal();//数量  
        public decimal FOrderPrice = new decimal();//订单价格
        public decimal itemPrice = new decimal();//材料价格  
        public decimal worktime = new decimal();//工时  
        public decimal fnetweight = new decimal();//净重,旧 
        public decimal FGrossWeight = new decimal();//净重 ，新  
        
        public List<bomTree> children = new List<bomTree>();
    }
     
}
